#!/usr/bin/env python2.6
"""
Scan database dumps and fill sqlite database (file database.db) with the data.

Database dumps are:
  * lib.libjoinedbooks.sql
  * lib.libbook.old.sql
"""

from pysqlite2 import dbapi2 as sqlite
import os.path
import re

def smart_split(s):
    elements = []
    buffer = ""
    is_string = False
    prev_sym = ""
    for x in v:
        if x == "'" and prev_sym != "\\":
            is_string = not is_string
            
        if x == "," and not is_string:
            # flush buffer
            if buffer[0] == "'" and buffer[-1] == "'":
                buffer = buffer[1:-1].replace(r"\'", "'")
                
            elements.append(buffer)
            buffer = ""
            continue
        
        buffer += x
        prev_sym = x
    
    elements.append(buffer)
    
    return elements

# create temporary table
db = sqlite.connect("database.db")
cursor = db.cursor()

queries = ("DROP TABLE IF EXISTS libbook ", "DROP TABLE IF EXISTS libjoinedbooks", 
           "DROP INDEX IF EXISTS libbook_filename")
for query in queries:
    cursor.execute(query)
    

# parse table with joined books
query = """
CREATE TABLE libjoinedbooks (
  Id INTEGER NOT NULL,
  Time timestamp NOT NULL,
  BadId int(11) NOT NULL default '0',
  GoodId int(11) NOT NULL default '0',
  PRIMARY KEY (Id)
)
"""
cursor.execute(query)

f = open(os.path.join("bases", "lib.libjoinedbooks.sql"), "r")
for line in f:
    if line.startswith("INSERT INTO `libjoinedbooks` VALUES"):
        line = unicode(line, "UTF-8")
        values = line.split("),(")
        
        values[0] = values[0].replace("INSERT INTO `libjoinedbooks` VALUES (", "")
        values[-1] = values[-1].replace(");", "")
        
        for v in values:
            # split
            elements = smart_split(v)
            
            if len(elements) != 4:
                print "Error number of elements: ", elements
                
            query = "INSERT INTO libjoinedbooks VALUES (%s)" % ",".join(["?" for x in xrange(4)])
            cursor.execute(query, elements)

f.close()

# parse old libbook tabke to extract filenames associated with the BookId
query = """
CREATE TABLE libbook (
  BookId INTEGER NOT NULL,
  FileName varchar(245) NOT NULL default '',
  FileSize INTEGER NOT NULL default '0',
  Time timestamp NOT NULL default CURRENT_TIMESTAMP,
  Title varchar(254) NOT NULL default '',
  SeqId INTEGER NOT NULL default '0',
  SeqNumb smallint(5) NOT NULL default '0',
  Lang varchar(2) NOT NULL default 'ru',
  FileType varchar(4) NOT NULL default 'fb2',
  Year smallint(6) NOT NULL default '0',
  Publisher varchar(99) NOT NULL default '',
  Deleted char(1) NOT NULL default '',
  Ver varchar(8) NOT NULL default '',
  Id varchar(127) NOT NULL,
  FileAuthor varchar(64) NOT NULL,
  N INTEGER NOT NULL default '0',
  keywords varchar(255) NOT NULL,
  PRIMARY KEY (BookId)
) 
"""
cursor.execute(query)
query = "CREATE INDEX libbook_filename ON libbook (FileName)"
cursor.execute(query)

f = open(os.path.join("bases", "lib.libbook.old.sql"), "r")

for line in f:
    if line.startswith("INSERT INTO `libbook` VALUES"):
        line = unicode(line, "UTF-8")
        values = line.split("),(")
        
        values[0] = values[0].replace("INSERT INTO `libbook` VALUES (", "")

        values[-1] = values[-1].replace(");", "")
        
        for v in values:
            # split
            elements = smart_split(v)
            
            if len(elements) != 17:
                print "Error number of elements: ", elements
                
            query = "INSERT INTO libbook VALUES (%s)" % ",".join(["?" for x in xrange(17)])
            cursor.execute(query, elements)
            
f.close()


db.commit()
